package com.egao.orderB.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.egao.common.core.damon.constant.UserConstant;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.utils.FileUploadUtil;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.*;
import com.egao.orderB.entity.*;
import com.egao.orderB.service.OceanService;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;

//import com.egao.orderB.mapper.OceanMapper;
//import javax.transaction.Transactional;

/**
 * 订舱管理服务实现类
 * @author : Doman
 * @date : 2021-09-06 16:20:23
 * @version ：1.0
 */
@Service
public class OceanServiceImpl /*extends ServiceImpl<OceanMapper, Ocean> */implements OceanService {

	@Autowired
	private OceanBiz oceanBiz;
	@Autowired
	private OceanProcessBiz oceanProcessBiz;
	@Autowired
	private CustomerBiz customerBiz;
  @Autowired
  private OceanFeeBiz oceanFeeBiz;
  @Autowired
  private OceanPreConfigurationBiz oceanPreConfigurationBiz;
  @Autowired
  private OceanTemplateBiz oceanTemplateBiz;
  @Autowired
  private OceanTemplateFeeBiz oceanTemplateFeeBiz;
  @Autowired
  private SettingsBiz settingsBiz;

  @Override
  public JsonResult list(PageResult2<T> page, Ocean ocean, User loginUser)  throws Exception{
      return oceanBiz.list(page,ocean,loginUser);
  }

  @Override
  public JsonResult listDel(PageResult2<T> page, Ocean ocean, User loginUser)  throws Exception{
      return oceanBiz.listDel(page,ocean,loginUser);
  }

  @Override
  public JsonResult list(Ocean ocean, User loginUser) {
      return JsonResult.ok().setData(oceanBiz.list(ocean,loginUser));
  }

  @Override
  public JsonResult listOpen(Ocean ocean, User loginUser) {
    Ocean b = oceanBiz.getById(ocean);
    OceanFee bf = new OceanFee();
    bf.setOceanId(b.getId());
    b.setOceanFeeList(oceanFeeBiz.list(bf, loginUser));
    OceanProcess billProcess = new OceanProcess();
    billProcess.setOceanId(b.getId());
    b.setOceanProcessList(oceanProcessBiz.list(billProcess));

    return JsonResult.ok().put("data", b);
  }

  @Override
  public JsonResult getById(Ocean ocean) {
      return JsonResult.ok().setData(oceanBiz.getById(ocean));
  }

  @Transactional
  @Override
  public JsonResult bookSpace(Ocean ocean, User loginUser, HttpServletRequest request) {
    if (Utils.isNotEmpty(ocean.getCustomerId())) {
      Customer cus = customerBiz.getById(new Customer(ocean.getCustomerId()+"",true));
      if(Utils.isEmpty(cus.getContacts()) ||Utils.isEmpty(cus.getContactsPhone()) || Utils.isEmpty(cus.getSalesmanId())
              || Utils.isEmpty(cus.getOperaterId()) ){
        return JsonResult.error("请先维护客户的联系人,业务员,操作!");
      }
      ocean.setContacts(cus.getContacts());
      ocean.setContactsPhone(cus.getContactsPhone());
      ocean.setOperator(cus.getOperater());
      ocean.setOperatorId(Integer.valueOf(cus.getOperaterId()));
      ocean.setSalesman(cus.getSalesman());
      ocean.setSalesmanId(Integer.valueOf(cus.getSalesmanId()));
    } else {
      ocean.setCustomerId(0);
      ocean.setOperatorId(0);
      ocean.setSalesmanId(0);
    }
    ocean.setState("订舱");
    int key= oceanBiz.save(ocean, loginUser);

    if (Utils.isNotEmpty(ocean.getPreConfigurationId())) {
      OceanPreConfiguration oceanPreConfiguration = new OceanPreConfiguration();
      oceanPreConfiguration.setId(ocean.getPreConfigurationId());
      oceanPreConfigurationBiz.remove(oceanPreConfiguration, loginUser);
    }

    OceanProcess bp = new OceanProcess();
    bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
    bp.setOceanId(key+"");
    bp.setState("订舱");
    oceanProcessBiz.save(bp, loginUser);
    return JsonResult.ok("订舱成功");
  }

  @Transactional
  @Override
  public JsonResult releaseTheCabin(Ocean ocean, User loginUser, HttpServletRequest request) {
    ocean.setState("放舱");
    oceanBiz.releaseTheCabin(ocean, loginUser);
    OceanProcess bp = new OceanProcess();
    bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
    bp.setOceanId(ocean.getId());
    bp.setState("放舱");
    oceanProcessBiz.save(bp, loginUser);
    return JsonResult.ok("放舱成功");
  }

  @Transactional
  @Override
  public JsonResult cutOrder(Ocean ocean, User loginUser, HttpServletRequest request) {
    Ocean byId = oceanBiz.getById(ocean);
    if (byId.getPaymentType().equals("预付")) {
      OceanTemplate oceanTemplate = new OceanTemplate();
      oceanTemplate.setCustomer(byId.getShip());
      oceanTemplate.setBoxType(byId.getBoxType());
      oceanTemplate.setPortOfDestination(byId.getEndPort());
      List<OceanTemplate> oceanTemplates = oceanTemplateBiz.list(oceanTemplate, loginUser);
      if (oceanTemplates != null && oceanTemplates.size() > 0) {
        OceanTemplate oceanTemplate1 = oceanTemplates.get(0);
        OceanTemplateFee oceanTemplateFee = new OceanTemplateFee();
        oceanTemplateFee.setOceanTemplateId(Integer.valueOf(oceanTemplate1.getId()));
        List<OceanTemplateFee> oceanTemplateFeeList = oceanTemplateFeeBiz.list(oceanTemplateFee, loginUser);
        if (oceanTemplateFeeList != null && oceanTemplateFeeList.size() > 0) {
          for (int i = 0; i < oceanTemplateFeeList.size(); i++) {
            OceanTemplateFee oceanTemplateFee1 = oceanTemplateFeeList.get(i);
            OceanFee oceanFeeYs = new OceanFee();
            oceanFeeYs.setOceanId(ocean.getId());
            oceanFeeYs.setType("应收");
            oceanFeeYs.setFeeName(oceanTemplateFee1.getFeeName());
            oceanFeeYs.setPrice(oceanTemplateFee1.getPrice());
            OceanFee oceanFeeYf = new OceanFee();
            oceanFeeYf.setOceanId(ocean.getId());
            oceanFeeYf.setType("应付");
            oceanFeeYf.setFeeName(oceanTemplateFee1.getFeeName());
            oceanFeeYf.setPrice(oceanTemplateFee1.getPrice());
            oceanFeeBiz.save(oceanFeeYs, loginUser);
            oceanFeeBiz.save(oceanFeeYf, loginUser);
          }
        }
      }
    }

    ocean.setState("截单");
    oceanBiz.cutOrder(ocean, loginUser);
    OceanProcess bp = new OceanProcess();
    bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
    bp.setOceanId(ocean.getId());
    bp.setState("截单");
    oceanProcessBiz.save(bp, loginUser);
    return JsonResult.ok("截单成功");
  }

  @Transactional
  @Override
  public JsonResult cutOff(Ocean ocean, User loginUser, HttpServletRequest request) {
    ocean.setState("截关");
    oceanBiz.cutOff(ocean, loginUser);
    OceanProcess bp = new OceanProcess();
    bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
    bp.setOceanId(ocean.getId());
    bp.setState("截关");
    oceanProcessBiz.save(bp, loginUser);
    return JsonResult.ok("截关成功");
  }

  @Transactional
  @Override
  public JsonResult save(Ocean ocean, User loginUser) {
    oceanBiz.save(ocean, loginUser);
    return JsonResult.ok("添加成功");
  }

  @Transactional
  @Override
  public JsonResult update(Ocean ocean, User loginUser) {
    Customer cus = customerBiz.getById(new Customer(ocean.getCustomerId()+"",true));
    if(Utils.isEmpty(cus.getContacts()) ||Utils.isEmpty(cus.getContactsPhone()) || Utils.isEmpty(cus.getSalesmanId())
            || Utils.isEmpty(cus.getOperaterId()) ){
      return JsonResult.error("请先维护客户的联系人,业务员,操作!");
    }
    ocean.setContacts(cus.getContacts());
    ocean.setContactsPhone(cus.getContactsPhone());
    ocean.setOperator(cus.getOperater());
    ocean.setOperatorId(Integer.valueOf(cus.getOperaterId()));
    ocean.setSalesman(cus.getSalesman());
    ocean.setSalesmanId(Integer.valueOf(cus.getSalesmanId()));
    if (oceanBiz.update(ocean, loginUser)) {
        return JsonResult.ok("修改成功");
    }
    return JsonResult.error("修改失败");
  }

  @Transactional
  @Override
  public JsonResult remove(Ocean ocean, User loginUser) {
      if (oceanBiz.remove(ocean, loginUser)) {
          return JsonResult.ok("删除成功");
      }
      return JsonResult.error("删除失败");
  }

  @Transactional
  @Override
  public void exportOcean(Ocean ocean, HttpServletResponse response) throws Exception {
    Ocean ocean2 = oceanBiz.getById(ocean);
    OceanFee oceanFee = new OceanFee();
    oceanFee.setOceanId(ocean2.getId());
    oceanFee.setType("应收");
    List<OceanFee> oceanFees = oceanFeeBiz.list(oceanFee, new User(1));

    // 根据模板创建excel工作簿
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(FileUploadUtil.UPLOAD_FILE_DIR + "ocean.xlsx");

    // 获取创建的工作簿第一页
    XSSFSheet sheet = xssfWorkbook.getSheet("Sheet1");
    sheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

    //创建列，即单元格Cell
    XSSFCell xssfCell;

    // 获取第一行
    XSSFRow xssfRow = sheet.getRow(4);
    // 先定义创建excel表头
    xssfCell = xssfRow.getCell(0); //创建单元格
   /* Font fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("华文楷体"); // 字体
    fontStyle.setFontHeightInPoints((short) 13.5); // 大小
//    fontStyle.setFontHeight((short)1500);
    XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);*/
    xssfCell.setCellValue("To:" + ocean2.getCustomer());

    xssfRow = sheet.getRow(6);
    xssfCell = xssfRow.getCell(0); //创建单元格
    xssfCell.setCellValue("提单号:" + ocean2.getBillOfLading());
    xssfRow = sheet.getRow(7);
    xssfCell = xssfRow.getCell(0); //创建单元格
    xssfCell.setCellValue("船名航次:" + ocean2.getVessel()+"/ "+ocean2.getVoyage());
    xssfRow = sheet.getRow(8);
    xssfCell = xssfRow.getCell(0); //创建单元格
    xssfCell.setCellValue("柜型:" + ocean2.getBoxType());

    OceanFee oceanFee1 = null;
    float sum = 0, usd = 0, cny = 0;
    if (oceanFees != null && oceanFees.size() > 0) {
      for (int i = 0; i < oceanFees.size(); i ++) {
        OceanFee oceanFee2 = oceanFees.get(i);
        xssfRow = sheet.getRow(11+i);
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue(oceanFee2.getFeeName());
        xssfCell = xssfRow.getCell(1); //创建单元格
        xssfCell.setCellValue(oceanFee2.getPrice());
        xssfCell = xssfRow.getCell(2); //创建单元格
        xssfCell.setCellValue(1);
        try {
          if (oceanFee2.getCurrency().equals("CNY")) {
            xssfCell = xssfRow.getCell(4); //创建单元格
            xssfCell.setCellValue(oceanFee2.getPrice());
            cny += Integer.valueOf(oceanFee2.getPrice());
          } else {
            xssfCell = xssfRow.getCell(3); //创建单元格
            xssfCell.setCellValue(oceanFee2.getPrice());
            usd += Integer.valueOf(oceanFee2.getPrice());
          }
          sum += Integer.valueOf(oceanFee2.getPrice());
        } catch (Exception e){

        }
        xssfCell = xssfRow.getCell(5); //创建单元格
        xssfCell.setCellValue(oceanFee2.getRemark());
      }
    }

    xssfRow = sheet.getRow(27);
    xssfCell = xssfRow.getCell(0); //创建单元格
    xssfCell.setCellValue("合计:"+ sum);
    xssfRow = sheet.getRow(28);
    xssfCell = xssfRow.getCell(0); //创建单元格
    xssfCell.setCellValue("应收USD:"+ usd);
    xssfCell = xssfRow.getCell(5); //创建单元格
    xssfCell.setCellValue("应收RMB:"+ cny);

    try {
      // 清空缓存
      response.reset();
      // 下载使用/未知类型
      response.setContentType("application/octet-stream");
      String fileName = "ocean.xls";
//			 fileName = URLEncoder.encode(fileName, "utf-8");
//			 fileName = fileName.replace("+", "%20");
      response.setHeader("Content-disposition", "attachment;filename=" + fileName);
      response.flushBuffer();
      // 创建Excel文件的输入流对象
      OutputStream os = response.getOutputStream();
      // 这时候把创建好的excel写入到输出流
      xssfWorkbook.write(os);
      // 关闭流
      os.flush();
      os.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  @Transactional
  @Override
  public void exportOceanFinance(List<Ocean> OceanList, HttpServletResponse response) throws Exception {

    // 根据模板创建excel工作簿
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(FileUploadUtil.UPLOAD_FILE_DIR + "ocean_finance.xlsx");

    // 获取创建的工作簿第一页
    XSSFSheet xssfSheet = xssfWorkbook.getSheet("Sheet1");
    xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题
    //创建列，即单元格Cell
    XSSFCell xssfCell;

    // 获取第一行
    XSSFRow xssfRow;

    XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    Font fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(false); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);

    Settings settings = new Settings();
    settings.setName("exchange_rate");
    settings = settingsBiz.getByName(settings);
    String exchange_rate = settings.getValue();
    for (int i = 0; i < OceanList.size(); i ++) {
      Ocean ocean = OceanList.get(i);
      String ysRmb = "", ysUsd = "", yfRmb = "", yfUsd = "";
      List<OceanFee> oceanFees = ocean.getOceanFeeList();
      if (oceanFees != null && oceanFees.size() > 0) {
        for (OceanFee oceanFee : oceanFees) {
          if (oceanFee.getType().equals("应收")) {
            if (oceanFee.getCurrency().equals("USD")) {
              ysUsd =  oceanFee.getPrice();
            } else if (oceanFee.getType().equals("CNY")) {
              ysRmb =  oceanFee.getPrice();
            }
          } else if (oceanFee.getType().equals("应付")) {
            if (oceanFee.getCurrency().equals("USD")) {
              yfUsd =  oceanFee.getPrice();
            } else if (oceanFee.getType().equals("CNY")) {
              yfRmb =  oceanFee.getPrice();
            }
          }
        }
      }
      List<String> row = CollUtil.newArrayList((i+1)+"", ocean.getCustomer(), ocean.getGoShipDay(), ocean.getBillOfLading()
              ,ysRmb, ysUsd, exchange_rate
              ,"function::SUM(E"+(i+4)+",PRODUCT(F"+(i+4)+",G"+(i+4)+"))"
              ,ocean.getBookingAgent()
              ,yfRmb, yfUsd, exchange_rate
              ,"function::SUM(J"+(i+4)+",L"+(i+4)+")"
//              ,"function::SUM(J"+(i+4)+",PRODUCT(K"+(i+4)+",L"+(i+4)+"))"
              ,"function::IMSUB(H"+(i+4)+",M"+(i+4)+")"
              ,"",ocean.getCusPlanDeposit(),"",""
              ,"",ocean.getPreStorageDeposit(),"",""
              ,"" ,"" ,"" ,"" ,"" ,""
              );

      String numColumns = ",4,5,6,7,9,10,11,12,13,15,19,";
      xssfRow = xssfSheet.createRow(i+3);
      setRow(xssfRow,row, cellStyle, numColumns);
    }
    xssfRow = xssfSheet.createRow(3+OceanList.size());
    for (int i = 0; i < 28; i++) {
      xssfCell = xssfRow.createCell(i); //创建单元格
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);
      xssfCell.setCellStyle(cellStyle);
      xssfCell.setCellType(CellType.NUMERIC);
    }

    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.getCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("合计:"); //设置单元格内容
    // 合并单元格
    CellRangeAddress cra =new CellRangeAddress(3+OceanList.size(), 3+OceanList.size(), 0, 3); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    String[] letters = new String[]{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
            "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
            "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
            "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB",
            "CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT",
            "CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL",
            "DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ"};
    int[] indexes = new int[]{4,5,7,9,10,12,13,15,19};
    for(int i =0; i < indexes.length; i ++) {
      xssfCell = xssfRow.getCell(indexes[i]); //创建单元格
//      xssfCell.setCellType(CellType.NUMERIC);
      xssfCell.setCellFormula("SUM("+letters[indexes[i]]+"4:"+letters[indexes[i]]+(3+OceanList.size())+")"); //设置单元格内容
    }

    try {
      // 清空缓存
      response.reset();
      // 下载使用/未知类型
      response.setContentType("application/octet-stream");
      String fileName = "ocean_finance.xls";
//			 fileName = URLEncoder.encode(fileName, "utf-8");
//			 fileName = fileName.replace("+", "%20");
      response.setHeader("Content-disposition", "attachment;filename=" + fileName);
      response.flushBuffer();
      // 创建Excel文件的输入流对象
      OutputStream os = response.getOutputStream();
      // 这时候把创建好的excel写入到输出流
      xssfWorkbook.write(os);
      // 关闭流
      os.flush();
      os.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  public void setRow(XSSFRow xssfRow,List<String> row, XSSFCellStyle cellStyle, String changeNum) {
    //创建每个单元格Cell，即列的数据
    if (Utils.isNotEmpty(row)) {
      for (int i = 0; i < row.size(); i ++) {
        XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
        String str = row.get(i);
        if(str==null) {
          str = "";
        }
        //https://blog.csdn.net/weixin_29100399/article/details/114203311
        if(str.indexOf("function::") != -1) {
          xssfCell.setCellFormula(str.substring(10,str.length())); //设置单元格内容
        } else if(changeNum.indexOf(","+i+",")!=-1) {
          xssfCell.setCellType(CellType.NUMERIC);
          if(Utils.isNotEmpty(row.get(i))) {
            xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
          } else {
            xssfCell.setCellValue(0);
          }
        } else {
          xssfCell.setCellValue(row.get(i)); //设置单元格内容
        }
        if (cellStyle != null) {
          xssfCell.setCellStyle(cellStyle);
        }
      }
    }
  }

}
